<?php
class ModelLocalisationCountry extends Model {
	public function addCountry($data) {
		$name = $this->db->escape($data['name']);
		$sql = "INSERT INTO " . DB_PREFIX . "country SET `name` = '" . $name . 
			"', iso_code = '" . $this->db->escape($data['iso_code']) . 
			"', iso_code_2 = '" . $this->db->escape($data['iso_code_2']) . 
			"', freight = '" . $this->db->escape($data['freight']) . 
			"', status = '" . (int)$data['status'] . "'";
		$this->db->query($sql);	
		$country_id = $this->db->getLastId();
      	
		foreach ($data['country_description'] as $language_id => $value) {
			$sql_desc = "INSERT INTO " . DB_PREFIX . "country_description SET country_id = '" . (int)$country_id . 
				"', language_id = '" . (int)$language_id . 
				"', display_name = '";
			if($value['display_name']){
				$sql_desc .= $this->db->escape($value['display_name']) . "'";
			}else{
				$sql_desc .= $name . "'";
			}
			$this->db->query($sql_desc);
		}
	}
	
	public function editCountry($country_id, $data) {
		$name = $this->db->escape($data['name']);
		$sql = "UPDATE " . DB_PREFIX . "country SET `name` = '" . $name . 
			"', iso_code = '" . $this->db->escape($data['iso_code']) . 
			"', iso_code_2 = '" . $this->db->escape($data['iso_code_2']) . 
			"', freight = '" . $this->db->escape($data['freight']) . 
			"', status = '" . (int)$data['status'] . 
			"' WHERE country_id = '" . (int)$country_id . "'";
		$this->db->query($sql);	
      	
		$this->db->query("DELETE FROM " . DB_PREFIX . "country_description WHERE country_id = '" . (int)$country_id . "'");
		foreach ($data['country_description'] as $language_id => $value) {
			$sql_desc = "INSERT INTO " . DB_PREFIX . "country_description SET country_id = '" . (int)$country_id . 
				"', language_id = '" . (int)$language_id . 
				"', display_name = '";
			if($value['display_name']){
				$sql_desc .= $this->db->escape($value['display_name']) . "'";
			}else{
				$sql_desc .= $name . "'";
			}
			$this->db->query($sql_desc);
		}
	}
	
	public function getCountry($country_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "country WHERE country_id = '" . (int)$country_id . "'");
		return $query->row;
	}

	public function getCountryDescription($country_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "country_description WHERE country_id = '" . (int)$country_id . "'");
		$result_data = array();
		foreach ($query->rows as $result) {
			$result_data[$result['language_id']] = array(
				'country_id'       => $result['country_id'],
				'language_id'      => $result['language_id'],
				'display_name'     => $result['display_name']
			);
		}
		return $result_data;
	}
	
	public function getCountries($data = array()) {
		$sql = "SELECT c.*, cd.display_name FROM " . DB_PREFIX . "country c LEFT JOIN " . 
			DB_PREFIX . "country_description cd ON c.country_id = cd.country_id AND cd.language_id = '" . 
			(int)$this->config->get('config_language_id') . "'";
			
		$sql_params = '';
		if (isset($data['iso_code'])) {
			$sql_params .= " AND LCASE(c.iso_code) LIKE '" . $this->db->escape(utf8_strtolower($data['iso_code'])) . "%'";
		}
		if (isset($data['iso_code_2'])) {
			$sql_params .= " AND LCASE(c.iso_code_2) LIKE '" . $this->db->escape(utf8_strtolower($data['iso_code_2'])) . "%'";
		}
		if (isset($data['status'])) {
			$sql_params .= " AND c.status = '" . (int)$data['status'] . "'";
		}
		if (isset($data['display_name'])) {
			$sql_params .= " AND LCASE(cd.display_name) LIKE '" . $this->db->escape(utf8_strtolower($data['display_name'])) . "%'";
		}
		if ($sql_params != '') {
			$sql_params = trim($sql_params);
			$sql_params = ltrim($sql_params, 'AND');
			$sql = $sql . " WHERE " . $sql_params;
		}
			
		$sql .= " ORDER BY cd.display_name";	
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}					
			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}	
		$query = $this->db->query($sql);
		return $query->rows;
	}
	
	public function getTotalCountries($data = array()) {
		$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "country c LEFT JOIN " . 
			DB_PREFIX . "country_description cd ON c.country_id = cd.country_id AND cd.language_id = '" . 
			(int)$this->config->get('config_language_id') . "'";
			
		$sql_params = '';
		if (isset($data['iso_code'])) {
			$sql_params .= " AND LCASE(c.iso_code) LIKE '" . $this->db->escape(utf8_strtolower($data['iso_code'])) . "%'";
		}
		if (isset($data['iso_code_2'])) {
			$sql_params .= " AND LCASE(c.iso_code_2) LIKE '" . $this->db->escape(utf8_strtolower($data['iso_code_2'])) . "%'";
		}
		if (isset($data['status'])) {
			$sql_params .= " AND c.status = '" . (int)$data['status'] . "'";
		}
		if (isset($data['display_name'])) {
			$sql_params .= " AND LCASE(cd.display_name) LIKE '" . $this->db->escape(utf8_strtolower($data['display_name'])) . "%'";
		}
		if ($sql_params != '') {
			$sql_params = trim($sql_params);
			$sql_params = ltrim($sql_params, 'AND');
			$sql = $sql . " WHERE " . $sql_params;
		}
		$query = $this->db->query($sql);
		return $query->row['total'];
	}

	public function getCountryAndDesc($country_id) {
		$query = $this->db->query("SELECT c.*, cd.display_name FROM " . DB_PREFIX . "country c LEFT JOIN " . 
			DB_PREFIX . "country_description cd ON c.country_id = cd.country_id AND cd.language_id = '" . 
			(int)$this->config->get('config_language_id') . "' WHERE c.country_id = '" . (int)$country_id . "'");
		return $query->row;
	}
}
?>